﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Text;

/// <summary>
/// Summary description for PlateTypeDAL
/// </summary>
public class PlateTypeDAL
{
    public static IList<PlateType> GetAll()
    {
        string sqlCommand = @"SELECT [ID]
                                  ,[Name]
                                  ,[Price]
                                  ,[Description]
                                  ,P.[LastUser]
                                  ,P.[LastUpdate]
                                  ,Lastname
                                  ,FirstName
                                FROM 
	                                [PlateType] P
                                LEFT JOIN Employee E ON E.IDCard=P.LastUser
                                ORDER BY Name	
                            ";
        List<PlateType> list = new List<PlateType>();
        try
        {
            using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sqlCommand))
            {
                while (dr.Read())
                {
                    PlateType plateType = new PlateType();
                    plateType.ID = SqlHelper.CheckIntNull(dr["ID"]);
                    plateType.Name = SqlHelper.CheckStringNull(dr["Name"]);
                    plateType.Price = SqlHelper.CheckIntNull(dr["Price"]);
                    plateType.Description = SqlHelper.CheckStringNull(dr["Description"]);
                    Employee emp = new Employee();
                    emp.Id = SqlHelper.CheckGuidNull(dr["LastUser"]);
                    emp.FirstName = SqlHelper.CheckStringNull(dr["Lastname"]);
                    emp.LastName = SqlHelper.CheckStringNull(dr["LastName"]);
                    plateType.LastUser = emp;
                    plateType.LastUpdate = Convert.ToDateTime(SqlHelper.CheckDateNull(dr["LastUpdate"]));
                    list.Add(plateType);
                }
            }
        }
        catch (Exception)
        {
            throw;
        }
        return list;
    }

    public static PlateType GetById(int id)
    {
        string sqlCommand = @"SELECT [ID]
                                  ,[Name]
                                  ,[Price]
                                  ,[Description]
                                  ,[LastUser]
                                  ,[LastUpdate]
                              FROM [PlateType]
                              WHERE ID=@ID";
        //parms
        SqlParameter parm = new SqlParameter("@ID", id);
        PlateType plateType = null;
        try
        {
            using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sqlCommand, parm))
            {
                while (dr.Read())
                {
                    plateType = new PlateType();
                    plateType.ID = SqlHelper.CheckIntNull(dr["ID"]);
                    plateType.Name = SqlHelper.CheckStringNull(dr["Name"]);
                    plateType.Price = SqlHelper.CheckIntNull(dr["Price"]);
                    plateType.Description = SqlHelper.CheckStringNull(dr["Description"]);
                    Employee emp = new Employee();
                    emp.Id = SqlHelper.CheckGuidNull(dr["LastUser"]);
                    plateType.LastUser = emp;
                    plateType.LastUpdate = Convert.ToDateTime(SqlHelper.CheckDateNull(dr["LastUpdate"]));
                    break;
                }
            }
        }
        catch (Exception)
        {
            throw;
        }
        return plateType;
    }    

//    private static string PrepareSQLInsert(PlateType plateType, ref SqlParameter[] parms)
//    {
//        string sql = @"INSERT INTO [[PlateType]
//                       ([Name]
//                       ,[Price]
//                       ,[Description]
//                       ,[LastUser]
//                       ,[LastUpdate])
//                 VALUES
//                       (@Name
//                       ,@Price
//                       ,@Description
//                       ,@LastUser
//                       ,GETDATE())";
//        //parms
//        parms = new SqlParameter[]
//                    {
//                        new SqlParameter("@Name",plateType.Name),
//                        new SqlParameter("@Price",plateType.Price),
//                        new SqlParameter("@Description",plateType.Description),
//                        new SqlParameter("@LastUser",plateType.LastUser.Id)                      
//                    };
//        return sql;
//    }

    private static string PrepareSQLUpdate(PlateType plateType, ref SqlParameter[] parms)
    {
        string sql = @"UPDATE [PlateType]
                       SET [Name] = @Name
                          ,[Price] = @Price
                          ,[Description] = @Description
                          ,[LastUser] = @LastUser
                          ,[LastUpdate] = GETDATE()
                     WHERE [ID] = @ID";
        //parms
        parms = new SqlParameter[]
                    {
                        new SqlParameter("@ID",plateType.ID),
                        new SqlParameter("@Name",plateType.Name),
                        new SqlParameter("@Price",plateType.Price),
                        new SqlParameter("@Description",plateType.Description),
                        new SqlParameter("@LastUser",plateType.LastUser.Id)                      
                    };
        return sql;
    }

    public static void Save(PlateType plateType)
    {
        string sqlCommand = String.Empty;
        SqlParameter[] parms = null;
        if (plateType.ID<=0)
        {
            //sqlCommand = PrepareSQLInsert(plateType, ref parms);
            return;
        }
        else
        {
            sqlCommand = PrepareSQLUpdate(plateType, ref parms);
        }

        try
        {
            SqlHelper.ExecuteNonQuery(CommandType.Text, sqlCommand, parms);
        }
        catch (Exception)
        {
            throw;
        }

    }

    public static bool InsertOrUpdate(PlateType plate)
    {

        #region Sql sentence
        string strSQLInsert = @"INSERT INTO [PlateType] (
	                                    
	                                    [Name],
                                        [Price],
	                                    [Description],
	                                    [LastUser],
	                                    [LastUpdate]
                                    ) VALUES (
	                                    
	                                    @Name,
	                                    @Description,
                                        @Price,
	                                    @LastUser,
	                                    GetDate()
                                    )";

        string strSQLUpdate = @"UPDATE [PlateType] SET
	                                    [Name] = @Name,
	                                    [Description] = @Description,
	                                    [LastUser] = @LastUser,
                                        [Price] = @Price,
	                                    [LastUpdate] = GetDate()
                                    WHERE
	                                    [ID] = @ID";
        #endregion

        //parms
        SqlParameter[] parms = new SqlParameter[]{
                        new SqlParameter("@ID",plate.ID),
                        new SqlParameter("@Name",plate.Name),
                        new SqlParameter("@Price",plate.Price),
                        new SqlParameter("@Description",plate.Description),
                        new SqlParameter("@LastUser",plate.LastUser.Id),
                    };

        string strSQL;
        //Insert case
        if (plate.ID == 0)
        {
            strSQL = strSQLInsert;
        }
        //delete case
        else
        {
            strSQL = strSQLUpdate;
        }

        try
        {
            return (SqlHelper.ExecuteNonQuery(CommandType.Text, strSQL, parms) > 0);
        }
        catch (Exception)
        {
            throw;
        }
    }

    public static string GetName(int id)
    {
        string strSQL = @"SELECT [Name]      
                          FROM [PlateType]
                          WHERE [ID]=@ID";

        //parms
        SqlParameter parm = new SqlParameter("@ID", id);

        try
        {
            object obj = SqlHelper.ExecuteScalar(CommandType.Text, strSQL, parm);
            if (obj == null) return String.Empty;
            return obj.ToString();
        }
        catch (Exception)
        {
            throw;
        }
    }


    public static bool Delete(int id)
    {
        string strSQL = "DELETE FROM [PlateType] WHERE [ID]=@ID";

        //parms
        SqlParameter parm = new SqlParameter("@ID", id);
        try
        {
            return (SqlHelper.ExecuteNonQuery(CommandType.Text, strSQL, parm) > 0);
        }
        catch (Exception)
        {
            throw;
        }
    }

    public static bool IsExistPlatType(string p)
    {
        #region Sql sentence
        string strSQL = @"Select count(ID) from [PlateType] WHERE Name=@Name";

        #endregion

        //parms
        SqlParameter[] parms = new SqlParameter[]{
                        new SqlParameter("@Name",p)
                    };
        try
        {
            return Int16.Parse(SqlHelper.ExecuteScalar(CommandType.Text, strSQL, parms).ToString()) > 0;
        }
        catch (Exception)
        {
            throw;
        }
    }

   
}